Last week ,i was working on performance issue of small table whose index index blevel was 1. I found cost calculation differs ,just decided to formulate few scenairo of cost calculation.
Table Stats:: Table: REMEDY_HIST_WITHOUT_HISTOGRAM
ROWS: 2382
BLOCKS: 13
AVGROWLEN: 18.00
INDEX STATS:: Index: INDX_REMEDY Col#: 1
BLEVEL : 1
LEAF BLOCK : 5
DISTINCT KEYS : 98
AVG NUM OF LEAF BLKS PER KEY : 1.00
AVG NUM OF DATA BLKS PER KEY : 1.00
CLUSTERING FACTOR : 163.00
***************************************
COST CALCULATION :
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
***************************************
TEST1:
Select * from test.REMEDY_HIST_WITHOUT_HISTOGRAM where seqno > 75
Access Path: index (RangeScan)
Index: INDX_REMEDY
resc_io: 43.00 resc_cpu: 545662
ix_sel: 0.2449 ix_sel_with_filters: 0.2449
Cost: 43.05 Resp: 43.05 Degree: 1
COST= 1 + ceil(5*0.2449) + ceil(163 * 0.2449)
= 1 + 2 + 40
= 43
Cost Calculation was perfect as per formula. Matches with the Cost Generated from 10053 trace.
TEST2:
Select * from test.REMEDY_HIST_WITHOUT_HISTOGRAM where seqno in(98,99)
Access Path: index (RangeScan)
Index: INDX_REMEDY
resc_io: 6.00 resc_cpu: 77062
ix_sel: 0.020408 ix_sel_with_filters: 0.020408
Cost: 6.01 Resp: 6.01 Degree: 1
COST = 1 + ceil( 5 * 0.020408) + ceil(163 * 0.020408)
= 1 + 1 + 4
= 6
Cost Calculation was perfect as per formula.
TEST3:
Select * from REMEDY_HIST_WITHOUT_HISTOGRAM where seqno=10
Index: INDX_REMEDY
resc_io: 3.00 resc_cpu: 32464
ix_sel: 0.010204 ix_sel_with_filters: 0.010204
Cost: 3.00 Resp: 3.00 Degree: 1
According to formula
COST = 1 + ceil(5 * 0.010204) + ceil(163 * 0.010204)
= 1 + 1 + 2
= 4
As we can see that according to formula Cost of Index scan is 4, where as 10053 and as well explain plan show Cost is 3.
How did this differ ?
Indexes where the blevel is set to 1 (so the index goes straight from the root block to the leaf blocks). The optimizer effectively ignores the blevel if every column in the index appears in an equality predicate.
This is an interesting case, as a root block split (which could happen due to one row in the table has been updated) would then push the cost of the index up by two—which could change the access path. This is just one of
many clues that small tables can be more important than large tables when you want to solve optimizer problems.
Sunday, July 5, 2009
Saturday, June 20, 2009
Impact of Number of blocks on cost Calculation
I had recently come across,where in an end user found query was running slowly.
The same query is run from multiple session on the server.
select i.Symbol Symbol,
TO_CHAR(i.StartTime,'YYYY-MM-DD,HH24:mi:ss') StartTime,
AvgBidSize,
AvgAskSize
from SCALETW_DBO.RawBidAskSizes t, SCALETW_DBO.IntervalMapTable i
where i.StartTime > TO_DATE('2009-04-02','YYYY-MM-DD') and StartTime < TO_DATE('2009-05-07', 'YYYY-MM-DD')
and Interval = 300
and t.IntervalMapTable_ID = i.IntervalMapTable_ID
and Symbol = :b2 order by i.StartTime
============
Plan Table
============
----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | SORT ORDER BY | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | HASH JOIN | | 2118 | 83K | 3491 | 00:00:49 |
| 3 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 4 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 5 | TABLE ACCESS FULL | RAWBIDASKSIZES | 6787K | 93M | 3368 | 00:00:48 |
-----------------------------------------------------------------------------------------------------
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 743 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
Table Stats::
Table: INTERVALMAPTABLE Alias: I
#Rows: 10814605 #Blks: 23917 AvgRowLen: 26.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 10814605 Nulls: 0 Density: 9.2468e-08 Min: 1031906069 Max: 1042684029
Index Stats::
Index: PK_INTERVALMAPTABLE495 Col#: 3
LVLS: 2 #LB: 11598 #DK: 11041507 LB/K: 1.00 DB/K: 1.00 CLUF: 35222.00
Index: SMTEST Col#: 1 4 2
LVLS: 2 #LB: 38782 #DK: 10733526 LB/K: 1.00 DB/K: 1.00 CLUF: 1496237.00
Index: TC_INTERVALMAPTABLE2311 Col#: 4
LVLS: 2 #LB: 23366 #DK: 4331 LB/K: 5.00 DB/K: 54.00 CLUF: 235943.00
Index: TC_INTERVALMAPTABLE2317 Col#: 4 1 2
LVLS: 2 #LB: 36842 #DK: 10695366 LB/K: 1.00 DB/K: 1.00 CLUF: 229583.00
***********************
Table Stats::
Table: RAWBIDASKSIZES Alias: T
#Rows: 6950280 #Blks: 8617 AvgRowLen: 14.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 6950280 Nulls: 0 Density: 1.4388e-07 Min: 1032164991 Max: 1042953912
Index Stats::
Index: PK_RAWBIDASKSIZES508 Col#: 3
LVLS: 2 #LB: 11276 #DK: 7348600 LB/K: 1.00 DB/K: 1.00 CLUF: 20482.00
***************************************
SINGLE TABLE ACCESS PATH
Table: RAWBIDASKSIZES Alias: T
Card: Original: 6950280 Rounded: 6950280 Computed: 6950280.00 Non Adjusted: 6950280.00
Access Path: TableScan
Cost: 3368.06 Resp: 3368.06 Degree: 0
Cost_io: 3233.00 Cost_cpu: 1404507597
Resp_io: 3233.00 Resp_cpu: 1404507597
Best:: AccessPath: TableScan
Cost: 3368.06 Degree: 1 Resp: 3368.06 Card: 6950280.00 Bytes: 0
***************************************
CPUSPEEDNW : 743
IOSeektim : 10
IOTrfSpeed : 4096
DFMRC : 8
SReadtim = IO Seektim + (db_block_size/IO Transfer Speed) = 10 + (16384/4096) = 14
MReadtim = IO Seektim + dfmrc * (db_block_size/IO Transfer Speed) = 10 + 8 * 4 = 42
#Mrds = 8617/8 -- this is default DFMRC = 1077.125
IO Cost = 1077.125 * (42/14) = 1077.125 * 3 = 3232 + 1 = 3233
CPU Cost = 1404507597/(743000*14) = 1404507597 / 10402000 = 135
Total CPU Cost = 3233 + 135 = 3368
I just increased the tried to make FULL Table Scan costlier by increasing num_blocks,
and let the optimizer use the index ,to check will that improve the performance.
execute dbms_stats.set_table_stats(OWNNAME=>'SCALETW_DBO',tabname=>'RAWBIDASKSIZES',numblks=>15000);
----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | NESTED LOOPS | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 3 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | RAWBIDASKSIZES | 1 | 17 | 2 | 00:00:41 |
| 5 | INDEX UNIQUE SCAN | PK_RAWBIDASKSIZES508 | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------------------------------------------------
I increased the num blocks of the table and we can see the query started using index. We found that query which was taking more than 1 hour, now completes in 2 minutes.I just locked the stats of the table for few weeks and then unlocked it and
regenerated the stats. I found that as volume of the table increased,as a result num blocks also increased and query found using index.
The same query is run from multiple session on the server.
select i.Symbol Symbol,
TO_CHAR(i.StartTime,'YYYY-MM-DD,HH24:mi:ss') StartTime,
AvgBidSize,
AvgAskSize
from SCALETW_DBO.RawBidAskSizes t, SCALETW_DBO.IntervalMapTable i
where i.StartTime > TO_DATE('2009-04-02','YYYY-MM-DD') and StartTime < TO_DATE('2009-05-07', 'YYYY-MM-DD')
and Interval = 300
and t.IntervalMapTable_ID = i.IntervalMapTable_ID
and Symbol = :b2 order by i.StartTime
============
Plan Table
============
----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | SORT ORDER BY | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | HASH JOIN | | 2118 | 83K | 3491 | 00:00:49 |
| 3 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 4 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 5 | TABLE ACCESS FULL | RAWBIDASKSIZES | 6787K | 93M | 3368 | 00:00:48 |
-----------------------------------------------------------------------------------------------------
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 743 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
Table Stats::
Table: INTERVALMAPTABLE Alias: I
#Rows: 10814605 #Blks: 23917 AvgRowLen: 26.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 10814605 Nulls: 0 Density: 9.2468e-08 Min: 1031906069 Max: 1042684029
Index Stats::
Index: PK_INTERVALMAPTABLE495 Col#: 3
LVLS: 2 #LB: 11598 #DK: 11041507 LB/K: 1.00 DB/K: 1.00 CLUF: 35222.00
Index: SMTEST Col#: 1 4 2
LVLS: 2 #LB: 38782 #DK: 10733526 LB/K: 1.00 DB/K: 1.00 CLUF: 1496237.00
Index: TC_INTERVALMAPTABLE2311 Col#: 4
LVLS: 2 #LB: 23366 #DK: 4331 LB/K: 5.00 DB/K: 54.00 CLUF: 235943.00
Index: TC_INTERVALMAPTABLE2317 Col#: 4 1 2
LVLS: 2 #LB: 36842 #DK: 10695366 LB/K: 1.00 DB/K: 1.00 CLUF: 229583.00
***********************
Table Stats::
Table: RAWBIDASKSIZES Alias: T
#Rows: 6950280 #Blks: 8617 AvgRowLen: 14.00
Column (#3): INTERVALMAPTABLE_ID(NUMBER)
AvgLen: 7.00 NDV: 6950280 Nulls: 0 Density: 1.4388e-07 Min: 1032164991 Max: 1042953912
Index Stats::
Index: PK_RAWBIDASKSIZES508 Col#: 3
LVLS: 2 #LB: 11276 #DK: 7348600 LB/K: 1.00 DB/K: 1.00 CLUF: 20482.00
***************************************
SINGLE TABLE ACCESS PATH
Table: RAWBIDASKSIZES Alias: T
Card: Original: 6950280 Rounded: 6950280 Computed: 6950280.00 Non Adjusted: 6950280.00
Access Path: TableScan
Cost: 3368.06 Resp: 3368.06 Degree: 0
Cost_io: 3233.00 Cost_cpu: 1404507597
Resp_io: 3233.00 Resp_cpu: 1404507597
Best:: AccessPath: TableScan
Cost: 3368.06 Degree: 1 Resp: 3368.06 Card: 6950280.00 Bytes: 0
***************************************
CPUSPEEDNW : 743
IOSeektim : 10
IOTrfSpeed : 4096
DFMRC : 8
SReadtim = IO Seektim + (db_block_size/IO Transfer Speed) = 10 + (16384/4096) = 14
MReadtim = IO Seektim + dfmrc * (db_block_size/IO Transfer Speed) = 10 + 8 * 4 = 42
#Mrds = 8617/8 -- this is default DFMRC = 1077.125
IO Cost = 1077.125 * (42/14) = 1077.125 * 3 = 3232 + 1 = 3233
CPU Cost = 1404507597/(743000*14) = 1404507597 / 10402000 = 135
Total CPU Cost = 3233 + 135 = 3368
I just increased the tried to make FULL Table Scan costlier by increasing num_blocks,
and let the optimizer use the index ,to check will that improve the performance.
execute dbms_stats.set_table_stats(OWNNAME=>'SCALETW_DBO',tabname=>'RAWBIDASKSIZES',numblks=>15000);
----------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3492 | |
| 1 | NESTED LOOPS | | 2118 | 83K | 3492 | 00:00:49 |
| 2 | TABLE ACCESS BY INDEX ROWID | INTERVALMAPTABLE | 2118 | 54K | 55 | 00:00:01 |
| 3 | INDEX RANGE SCAN | TC_INTERVALMAPTABLE2317| 2118 | | 10 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | RAWBIDASKSIZES | 1 | 17 | 2 | 00:00:41 |
| 5 | INDEX UNIQUE SCAN | PK_RAWBIDASKSIZES508 | 1 | | 1 | 00:00:01 |
-----------------------------------------------------------------------------------------------------
I increased the num blocks of the table and we can see the query started using index. We found that query which was taking more than 1 hour, now completes in 2 minutes.I just locked the stats of the table for few weeks and then unlocked it and
regenerated the stats. I found that as volume of the table increased,as a result num blocks also increased and query found using index.
Thursday, May 21, 2009
High Water mark on objects in ASSM Tablespaces
This article discusses about High Water mark on objects in ASSM tablespace and the impact that it can have on database performance.
In order to do a full tablescan ,oracle needs to read all the blocks below high water mark.
In general, when a table or index segment is first created, space for that segment will be preallocated from a datafile but very little of the space will be formatted for use. As data arrives, blocks will be formatted a few at a time.
In the simplest setup, Oracle would format “the next five” blocks from the preallocated space as the need arose, and the object’s high water mark (HWM) would be adjusted to show how many blocks had been formatted and were available for use.
With the arrival of ASSM in 9i, Oracle formats groups of adjacent blocks (typically 16, it seems) at a time.The high water mark still identifies the highest formatted block in the segment, but ASSM randomizes the allocationslightly, so that unformatted holes (16 blocks, or multiples thereof) can appear in the middle of the object.ASSM also allocates one or two bitmap space management blocks per extent.
Creating LMT with extent size uniform
--------------------------------------------------------------
create tablespace TBS_LMT_UNIFORM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_UNIFORM_01.dbf' size 200M reuse
extent management local uniform size 800K segment space management auto;
Create Table Kadhiresan_LMT_UNIFORM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_UNIFORM;
Creating LMT with Autoallocate
--------------------------------------------------------------
create tablespace TBS_LMT_SYSTEM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_SYSTEM_01.dbf' size 200M reuse
extent management local AUTOALLOCATEsegment space management auto;
Create Table Kadhiresan1_LMT_SYSTEM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_SYSTEM;
SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.
SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.
SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');
TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 0 100
KADHIRESAN1_LMT_SYSTEM 0 8
SQL> insert into KADHIRESAN_LMT_UNIFORM values('1','KADHIRESAN');
1 row created.
SQL> insert into KADHIRESAN1_LMT_SYSTEM values('1','KADHIRESAN');
1 row created.
SQL> commit;
SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.
SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.
SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');
TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 23 77
KADHIRESAN1_LMT_SYSTEM 5 3
SQL> Select * from KADHIRESAN_LMT_UNIFORM;
Statistics
==========================================================
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> Select * from KADHIRESAN1_LMT_SYSTEM;
Statistics
==========================================================
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
0 sorts (disk)
1 rows processed
After Table has been Analyzed ,the "BLOCKS" field in USER_Tables will contain values.The value represents no of blocks held in the table -that is number of blocks from start of table and the high water mark.The field "Empyt_blocks" holds the number of blocks allocated to the table that are above the high water mark.
In order to do a full tablescan ,oracle needs to read all the blocks below high water mark.
In general, when a table or index segment is first created, space for that segment will be preallocated from a datafile but very little of the space will be formatted for use. As data arrives, blocks will be formatted a few at a time.
In the simplest setup, Oracle would format “the next five” blocks from the preallocated space as the need arose, and the object’s high water mark (HWM) would be adjusted to show how many blocks had been formatted and were available for use.
With the arrival of ASSM in 9i, Oracle formats groups of adjacent blocks (typically 16, it seems) at a time.The high water mark still identifies the highest formatted block in the segment, but ASSM randomizes the allocationslightly, so that unformatted holes (16 blocks, or multiples thereof) can appear in the middle of the object.ASSM also allocates one or two bitmap space management blocks per extent.
Creating LMT with extent size uniform
--------------------------------------------------------------
create tablespace TBS_LMT_UNIFORM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_UNIFORM_01.dbf' size 200M reuse
extent management local uniform size 800K segment space management auto;
Create Table Kadhiresan_LMT_UNIFORM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_UNIFORM;
Creating LMT with Autoallocate
--------------------------------------------------------------
create tablespace TBS_LMT_SYSTEM
datafile '/sbclocal/app/oracle/admin/NADEEM1/data/TBS_LMT_SYSTEM_01.dbf' size 200M reuse
extent management local AUTOALLOCATEsegment space management auto;
Create Table Kadhiresan1_LMT_SYSTEM
(Empno char(11),
Empname char(20))
tablespace TBS_LMT_SYSTEM;
SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.
SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.
SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');
TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 0 100
KADHIRESAN1_LMT_SYSTEM 0 8
SQL> insert into KADHIRESAN_LMT_UNIFORM values('1','KADHIRESAN');
1 row created.
SQL> insert into KADHIRESAN1_LMT_SYSTEM values('1','KADHIRESAN');
1 row created.
SQL> commit;
SQL> Analyze table KADHIRESAN_LMT_UNIFORM compute statistics;
Table analyzed.
SQL> Analyze table KADHIRESAN1_LMT_SYSTEM compute statistics;
Table analyzed.
SQL> select Table_name,blocks,empty_blocks from dba_tables where table_name in('KADHIRESAN_LMT_UNIFORM','KADHIRESAN1_LMT_SYSTEM');
TABLE_NAME BLOCKS EMPTY_BLOCKS
============================== ========== ============
KADHIRESAN_LMT_UNIFORM 23 77
KADHIRESAN1_LMT_SYSTEM 5 3
SQL> Select * from KADHIRESAN_LMT_UNIFORM;
Statistics
==========================================================
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> Select * from KADHIRESAN1_LMT_SYSTEM;
Statistics
==========================================================
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
607 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
sorts (memory)
0 sorts (disk)
1 rows processed
After Table has been Analyzed ,the "BLOCKS" field in USER_Tables will contain values.The value represents no of blocks held in the table -that is number of blocks from start of table and the high water mark.The field "Empyt_blocks" holds the number of blocks allocated to the table that are above the high water mark.
Friday, May 8, 2009
Difference Controlfile and Backup Controlfile
Operations during backup controlfile.
--------------------------------------------------------------------------------
1) Enqueue control file resource is held on controlfile so that no changes happen.
2) Checkpoint and SCN is till the time of backup.
3) Copy of Controlfile.
There are mutilple ways to perform backup activity of controlfile.
1) Shutdown database and O/S Copy controlfile in different location.
2) Alter database backup controlfile to trace -- Create a text file.
3) Alter database backup controlfile to '######################';
When Database is offline(normal/Immediate), controlfile backup will be consistent and stop scn will be checkpoint scn.
When Database is offline(abort), controlfile backup will be consistent and stop scn will be set to infinity.
When Database is online , if we take backup of controlfile , controlfile will be consistent till that point in time and stop scn will be infinity.
How does Oracle know, wheather controlfile was backup controlfile.
1) Checkpoint is older then checkpoint value on datafile, if controlfile backup was older than datafile backup.
2) Filetype of backup controlfile is 4 , whereas filetype of controlfile is 1.
Dump of header of controlfiles. Online Controlfile
------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=212=0xd4, File size=920=0x398
File Number=0, Blksiz=16384, File Type=1 CONTROL
Backup Controlfile
-------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=215=0xd7, File size=920=0x398
File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL
Just for Info:
---------------------------------------------
FileType=1 === > CONTROLFILE
FileType=2 === > LOGFILE
FileType=3 === > DATAFILE
FileType=4 === > BACKUP CONTROLFILE
FileType=4 === > Futuristic use
FileType=6 === > TEMPFILE
--------------------------------------------------------------------------------
1) Enqueue control file resource is held on controlfile so that no changes happen.
2) Checkpoint and SCN is till the time of backup.
3) Copy of Controlfile.
There are mutilple ways to perform backup activity of controlfile.
1) Shutdown database and O/S Copy controlfile in different location.
2) Alter database backup controlfile to trace -- Create a text file.
3) Alter database backup controlfile to '######################';
When Database is offline(normal/Immediate), controlfile backup will be consistent and stop scn will be checkpoint scn.
When Database is offline(abort), controlfile backup will be consistent and stop scn will be set to infinity.
When Database is online , if we take backup of controlfile , controlfile will be consistent till that point in time and stop scn will be infinity.
How does Oracle know, wheather controlfile was backup controlfile.
1) Checkpoint is older then checkpoint value on datafile, if controlfile backup was older than datafile backup.
2) Filetype of backup controlfile is 4 , whereas filetype of controlfile is 1.
Dump of header of controlfiles. Online Controlfile
------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=212=0xd4, File size=920=0x398
File Number=0, Blksiz=16384, File Type=1 CONTROL
Backup Controlfile
-------------------------------------------------------------
Compatibility Vsn = 169869312=0xa200000
Db ID=1418608916=0x548e4114, Db Name='PRASAD1'
Activation ID=0=0x0
Control Seq=215=0xd7, File size=920=0x398
File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL
Just for Info:
---------------------------------------------
FileType=1 === > CONTROLFILE
FileType=2 === > LOGFILE
FileType=3 === > DATAFILE
FileType=4 === > BACKUP CONTROLFILE
FileType=4 === > Futuristic use
FileType=6 === > TEMPFILE
Sunday, April 26, 2009
Introduction
Hi ,
My name is Kadhiresan Chettiar and I've been working with Oracle databases for about 6 years. I am going to share all the information I learned about the Oracle databases and database administration here. I think, the more we share the more we learn new things about the Information Technology. So lets share our knowledge with the blogging community.
Regards
Kadhiresan Chettiar
Friday, April 24, 2009
Role of High_value and Low_value column of dba_tab_columns while generating execution plan.
Our support team called us, the Oracle DBA team, for a problem. The problem was a program, was running OK before but it runs very slowly now, but they said that nothing has changed about the program.Previously query used to take 2 millisec and now taking one hour to execute.
Basic questions araised. what caused performace of query to degrade.
I gathered query plan which was executing at that moment.
Current SQL statement :
-------------------------------------------
SELECT m.corr_acc_no, m.sub_acc_no "Accoun t No.", m.short_code "Short Code", m.stmt_date "STmt. Date", m.status "Status", trunc(m.sys_entry_date) "Load Date", count(*) "Entries"
FROM SCRTLMAPP.message_header m
WHERE m.sys_entry_date >= trunc(sysdate)-4 AND m.status >= 100
GROUP BY m.corr_acc_no, m.sub_acc_no, m.short_code, m.stmt_date, m.status, trunc(m.sys_entry_date)
ORDER BY 1,2,3,5
--------------------------------------------+------------------------------------------+
: Id : Operation : Name : Rows: Bytes : Cost : Time :
--------------------------------------------+-----------------------------------------+
: 0 : SELECT STATEMENT : : : : 164K : :
: 1 : SORT GROUP BY : : 2 : 92 : 164K : 00:34:34:
: 2 : TABLE ACCESS FULL : MESSAGE_HEADER: 2 : 92 : 164K : 00:34:34:
--------------------------------------------+------------------------------------------+
Predicate Information:
----------------------
2 - filter(("M"."STATUS">=100 AND "M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4))
--
With Sql_id we found previous query plan.
----------------------------------------------------------------------------------------------
Id :Operation : Name :Rows :Bytes :Cost :Time
--------------------------------------------------------------------------------------------+
0 :SELECT STATEMENT : : : : 7 : :
1 :SORT GROUP BY : : 1 : 46 : 7 : 00:00:01 :
2 :TABLE ACCESS BY INDEX ROWID :MESSAGE_HEADER :1: 46: 6: 00:00:01
3 :INDEX SKIP SCAN :MESSAGE_HEADERIXB :1 : : 5 :00:00:01
---------------------------------------------------------+-----------------------------------+
Predicate Information:
---------------------
-2 - filter("M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4)
-3 - access("M"."STATUS">=100)3 - filter("M"."STATUS">=100)
Query was previously using Index Skip scan and Currently it was using Full table Scan.
Question arises what caused Change?
We found that Tables stats was gather pervious day.So I have revert back the stats. I found Query started using indexes. So i generated 10053 trace of the query with different stats that generated different Plans.
So i generated 10053 trace of the query with different stats that generated different Plans.
Query using Index (Part of Trace File)
------------------------------------------------------------------
*****************************SYSTEM STATISTICS INFORMATION
***************************** Using NOWORKLOAD Stats
CPUSPEED: 1043 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
--------------------------------------------------------------------
Table Stats::
Table: MESSAGE_HEADER Alias: M
#Rows: 9538250 #Blks: 762025 AvgRowLen: 162.00
Index Stats::
Index: MESSAGE_HEADERIXA Col#: 4
LVLS: 2 #LB: 50790 #DK: 9099770 LB/K: 1.00 DB/K: 1.00 CLUF: 351680.00
Index: MESSAGE_HEADERIXB Col#: 14 26 4
LVLS: 2 #LB: 41930 #DK: 9494790 LB/K: 1.00 DB/K: 1.00 CLUF: 371550.00
Index: MESSAGE_HEADERIXC Col#: 14 16 9
LVLS: 2 #LB: 26160 #DK: 4865 LB/K: 5.00 DB/K: 632.00 CLUF: 3078500.00
Index: MESSAGE_HEADERIXD Col#: 3 4
LVLS: 2 #LB: 41540 #DK: 9966350 LB/K: 1.00 DB/K: 1.00 CLUF: 344500.00
Index: MESSAGE_HEADERIXZ Col#: 4 1 26 14 16
LVLS: 3 #LB: 144050 #DK: 9477320 LB/K: 1.00 DB/K: 1.00 CLUF: 457530.00
Index: MESSAGE_HEADER_IND_KEY Col#: 1 2 3 4
LVLS: 3 #LB: 75470 #DK: 9490520 LB/K: 1.00 DB/K: 1.00 CLUF: 3446280.00
Column (#9): CORR_ACC_NO(NUMBER)
AvgLen: 7.00 NDV: 3853 Nulls: 0 Density: 8.2645e-04 Min: 0 Max: 100077909
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 136
Column (#1): SUB_ACC_NO(VARCHAR2)
AvgLen: 16.00 NDV: 3441 Nulls: 0 Density: 0.0011136
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 158
Column (#24): SHORT_CODE(VARCHAR2)
AvgLen: 4.00 NDV: 25 Nulls: 0 Density: 5.2504e-08
Histogram: Freq #Bkts: 25 UncompBkts: 5359 EndPtVals: 25
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 14 Nulls: 0 Density: 0.071429 Min: -1 Max: 42
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#2): STMT_DATE(DATE)
AvgLen: 8.00 NDV: 106 Nulls: 0 Density: 5.2504e-08 Min: 2453776 Max: 2454936
Histogram: Freq #Bkts: 106 UncompBkts: 5359 EndPtVals: 106
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167722.77 Resp: 167722.77 Degree: 0
Cost_io: 166695.00 Cost_cpu: 12866550691
Resp_io: 166695.00 Resp_cpu: 12866550691
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 3
SS io: 3.00 vs. table scan io: 166695.00
Skip Scan chosen Access Path: index (SkipScan)
Index: MESSAGE_HEADERIXB resc_io: 6.00 resc_cpu: 44032
ix_sel: 5.2421e-08 ix_sel_with_filters: 5.2421e-08
Cost: 6.00 Resp: 6.00 Degree: 1
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 144054.00 resc_cpu: 2921337020
ix_sel: 1 ix_sel_with_filters: 5.2421e-08 Cost: 144325.45
Resp: 144325.45 Degree: 1
We can see that Oracle using skip scan.
It used because "M"."STATUS">=100 is out of range of Min=-1 and Max=42 value of Status columns.
Query using Full table scan(Part of Trace File)
------------------------------------------------------------------------------------------
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 18 Nulls: 0 Density: 0.071429 Min: -1 Max: 1103
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167800.75 Resp: 167800.75 Degree: 0
Cost_io: 166695.00 Cost_cpu: 13842794223
Resp_io: 166695.00 Resp_cpu: 13842794223
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 723680
SS io: 723680.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXB
resc_io: 73258.00 resc_cpu: 3619052852
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 73587.41 Resp: 73587.41 Degree: 1
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 196172.00 resc_cpu: 4557617793
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 196576.38 Resp: 196576.38 Degree: 1
We can see that Oracle rejected skip scan and selected full table scan.
It used Full Table scan because "M"."STATUS">=100 is in range of Min=-1 and Max=1103 value of Status columns.
We know why plan had changed.
So i tried to query the status column to find max value of status .It turned out to be 42.
Question araised how come max value 1103 was gathered during stats.End user purged data after gathering stats.
Min and Max value in 10053 is nothing but high_value and low_value of dba_tab_columns/dba_tab_col_statistics.
we can also find that density and cardinality is same in both the case.
So I could conculde that Min and Max value play a important role in generaing a excution plan.
Basic questions araised. what caused performace of query to degrade.
I gathered query plan which was executing at that moment.
Current SQL statement :
-------------------------------------------
SELECT m.corr_acc_no, m.sub_acc_no "Accoun t No.", m.short_code "Short Code", m.stmt_date "STmt. Date", m.status "Status", trunc(m.sys_entry_date) "Load Date", count(*) "Entries"
FROM SCRTLMAPP.message_header m
WHERE m.sys_entry_date >= trunc(sysdate)-4 AND m.status >= 100
GROUP BY m.corr_acc_no, m.sub_acc_no, m.short_code, m.stmt_date, m.status, trunc(m.sys_entry_date)
ORDER BY 1,2,3,5
--------------------------------------------+------------------------------------------+
: Id : Operation : Name : Rows: Bytes : Cost : Time :
--------------------------------------------+-----------------------------------------+
: 0 : SELECT STATEMENT : : : : 164K : :
: 1 : SORT GROUP BY : : 2 : 92 : 164K : 00:34:34:
: 2 : TABLE ACCESS FULL : MESSAGE_HEADER: 2 : 92 : 164K : 00:34:34:
--------------------------------------------+------------------------------------------+
Predicate Information:
----------------------
2 - filter(("M"."STATUS">=100 AND "M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4))
--
With Sql_id we found previous query plan.
----------------------------------------------------------------------------------------------
Id :Operation : Name :Rows :Bytes :Cost :Time
--------------------------------------------------------------------------------------------+
0 :SELECT STATEMENT : : : : 7 : :
1 :SORT GROUP BY : : 1 : 46 : 7 : 00:00:01 :
2 :TABLE ACCESS BY INDEX ROWID :MESSAGE_HEADER :1: 46: 6: 00:00:01
3 :INDEX SKIP SCAN :MESSAGE_HEADERIXB :1 : : 5 :00:00:01
---------------------------------------------------------+-----------------------------------+
Predicate Information:
---------------------
-2 - filter("M"."SYS_ENTRY_DATE">=TRUNC(SYSDATE@!)-4)
-3 - access("M"."STATUS">=100)3 - filter("M"."STATUS">=100)
Query was previously using Index Skip scan and Currently it was using Full table Scan.
Question arises what caused Change?
We found that Tables stats was gather pervious day.So I have revert back the stats. I found Query started using indexes. So i generated 10053 trace of the query with different stats that generated different Plans.
So i generated 10053 trace of the query with different stats that generated different Plans.
Query using Index (Part of Trace File)
------------------------------------------------------------------
*****************************SYSTEM STATISTICS INFORMATION
***************************** Using NOWORKLOAD Stats
CPUSPEED: 1043 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
--------------------------------------------------------------------
Table Stats::
Table: MESSAGE_HEADER Alias: M
#Rows: 9538250 #Blks: 762025 AvgRowLen: 162.00
Index Stats::
Index: MESSAGE_HEADERIXA Col#: 4
LVLS: 2 #LB: 50790 #DK: 9099770 LB/K: 1.00 DB/K: 1.00 CLUF: 351680.00
Index: MESSAGE_HEADERIXB Col#: 14 26 4
LVLS: 2 #LB: 41930 #DK: 9494790 LB/K: 1.00 DB/K: 1.00 CLUF: 371550.00
Index: MESSAGE_HEADERIXC Col#: 14 16 9
LVLS: 2 #LB: 26160 #DK: 4865 LB/K: 5.00 DB/K: 632.00 CLUF: 3078500.00
Index: MESSAGE_HEADERIXD Col#: 3 4
LVLS: 2 #LB: 41540 #DK: 9966350 LB/K: 1.00 DB/K: 1.00 CLUF: 344500.00
Index: MESSAGE_HEADERIXZ Col#: 4 1 26 14 16
LVLS: 3 #LB: 144050 #DK: 9477320 LB/K: 1.00 DB/K: 1.00 CLUF: 457530.00
Index: MESSAGE_HEADER_IND_KEY Col#: 1 2 3 4
LVLS: 3 #LB: 75470 #DK: 9490520 LB/K: 1.00 DB/K: 1.00 CLUF: 3446280.00
Column (#9): CORR_ACC_NO(NUMBER)
AvgLen: 7.00 NDV: 3853 Nulls: 0 Density: 8.2645e-04 Min: 0 Max: 100077909
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 136
Column (#1): SUB_ACC_NO(VARCHAR2)
AvgLen: 16.00 NDV: 3441 Nulls: 0 Density: 0.0011136
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 158
Column (#24): SHORT_CODE(VARCHAR2)
AvgLen: 4.00 NDV: 25 Nulls: 0 Density: 5.2504e-08
Histogram: Freq #Bkts: 25 UncompBkts: 5359 EndPtVals: 25
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 14 Nulls: 0 Density: 0.071429 Min: -1 Max: 42
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#2): STMT_DATE(DATE)
AvgLen: 8.00 NDV: 106 Nulls: 0 Density: 5.2504e-08 Min: 2453776 Max: 2454936
Histogram: Freq #Bkts: 106 UncompBkts: 5359 EndPtVals: 106
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167722.77 Resp: 167722.77 Degree: 0
Cost_io: 166695.00 Cost_cpu: 12866550691
Resp_io: 166695.00 Resp_cpu: 12866550691
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 3
SS io: 3.00 vs. table scan io: 166695.00
Skip Scan chosen Access Path: index (SkipScan)
Index: MESSAGE_HEADERIXB resc_io: 6.00 resc_cpu: 44032
ix_sel: 5.2421e-08 ix_sel_with_filters: 5.2421e-08
Cost: 6.00 Resp: 6.00 Degree: 1
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (skip-scan)
SS sel: 5.2421e-08 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Using prorated density: 5.2421e-08 of col #26 as selectivity of out-of-range value pred
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 144054.00 resc_cpu: 2921337020
ix_sel: 1 ix_sel_with_filters: 5.2421e-08 Cost: 144325.45
Resp: 144325.45 Degree: 1
We can see that Oracle using skip scan.
It used because "M"."STATUS">=100 is out of range of Min=-1 and Max=42 value of Status columns.
Query using Full table scan(Part of Trace File)
------------------------------------------------------------------------------------------
Column (#26): STATUS(NUMBER)
AvgLen: 3.00 NDV: 18 Nulls: 0 Density: 0.071429 Min: -1 Max: 1103
Histogram: HtBal #Bkts: 10 UncompBkts: 10 EndPtVals: 3
Column (#30): SYS_ENTRY_DATE(DATE)
AvgLen: 8.00 NDV: 635828 Nulls: 0 Density: 3.1307e-06 Min: 2453305 Max: 2454933
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
Access Path: TableScan
Cost: 167800.75 Resp: 167800.75 Degree: 0
Cost_io: 166695.00 Cost_cpu: 13842794223
Resp_io: 166695.00 Resp_cpu: 13842794223
kkofmx: index filter:"M"."STATUS">=100
kkofmx: index filter:"M"."STATUS">=100
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 723680
SS io: 723680.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXB
resc_io: 73258.00 resc_cpu: 3619052852
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 73587.41 Resp: 73587.41 Degree: 1
Access Path: index (skip-scan)
SS sel: 0.071429 ANDV (#skips): 9535627
SS io: 9535627.00 vs. table scan io: 166695.00
Skip Scan rejected
Access Path: index (FullScan)
Index: MESSAGE_HEADERIXZ
resc_io: 196172.00 resc_cpu: 4557617793
ix_sel: 1 ix_sel_with_filters: 0.071429
Cost: 196576.38 Resp: 196576.38 Degree: 1
We can see that Oracle rejected skip scan and selected full table scan.
It used Full Table scan because "M"."STATUS">=100 is in range of Min=-1 and Max=1103 value of Status columns.
We know why plan had changed.
So i tried to query the status column to find max value of status .It turned out to be 42.
Question araised how come max value 1103 was gathered during stats.End user purged data after gathering stats.
Min and Max value in 10053 is nothing but high_value and low_value of dba_tab_columns/dba_tab_col_statistics.
we can also find that density and cardinality is same in both the case.
So I could conculde that Min and Max value play a important role in generaing a excution plan.
Subscribe to:
Posts (Atom)